<?php
/**
 * The database connection
 * @author lhe
 */
class NumberDB
{
    /**
     * mysql object
     * @var PDO
     */
    private $_db = null;
    /**
     * The authentication information
     * @var array
     */
    private $_db_auth = array(
        'host' => 'localhost',
        'db' => 'lotto',
        'user' => 'root',
        'pass' => ''
    );
    /**
     * connect to mysql
     *
     * @return PDO
     */
    private function _connect()
    {
        $this->_db = new PDO('mysql:host=' . $this->_db_auth['host'] . ';dbname=' . $this->_db_auth['db'], $this->_db_auth['user'], $this->_db_auth['pass']);
        return $this->_db;
    }
    /**
     * disconnect from mysql
     *
     * @return HydraSession
     */
    private function _disconnect()
    {
        $this->_db = null;
        return $this;
    }
    /**
     * Sql executor
     *
     * @param string $sql   The sql statement
     * @param array  $param The parameters for the sql statement
     *
     * @return multitype:
     */
    private function _exesql($sql, $param = array())
    {
        $sth = $this->_connect()->prepare($sql);
        $sth->execute($param);
        $result = $sth->fetchAll();
        $this->_disconnect();
        return $result;
    }
    /**
     * getting the game type information
     * 
     * @param string $typeName the type name
     * 
     * @throws Exception
     * @return mixed
     */
    public function getGameType($id, $typeName = '')
    {
        if(trim(id) === '' && trim($typeName) === '')
            throw new Exception('We need at least one of these: id or typename!');
        $field = 'name';
        $value = trim($typeName);
        if(trim(id) !== '')
        {
            $field = 'id';
            $value = trim(id); 
        }
        $sth = $this->_connect()->prepare('select * from gametype where ' . $field . ' = ?');
        $sth->execute(array($value));
        $result = $sth->fetch(PDO::FETCH_ASSOC);
        if(!isset($result['id']))
            throw new Exception('We did get the id');
        return $result;
    }
    /**
     * Find the permutation from the provided numbers
     * 
     * @param array $numbers The permutation we are searching on
     * 
     * @return mix The row data of the permutation
     */
    public function findPermutation($numbers = array())
    {
        if (!is_array($numbers))
            throw new Exception("We needed an array of integer here!");
        $sth = $this->_connect()->prepare('select * from numbers where flat = ?');
        $sth->execute(array("'" . implode(', ', $numbers) . "'"));
        $result = $sth->fetch(PDO::FETCH_ASSOC);
        return $result;
    }
    /**
     * Finding the permutation and gametype relationship
     * 
     * @param Int $permId     The permutation id
     * @param Int $gameTypeId The game type id
     * 
     * @return mixed
     */
    public function getPermGameType($permId, $gameTypeId)
    {
        $sth = $this->_connect()->prepare('select * from numbers_gametype where numbersId = ? and gametypeid = ?');
        $sth->execute(array($permId, $gameTypeId));
        $result = $sth->fetch(PDO::FETCH_ASSOC);
        return $result;
    }
    /**
     * insert new numbers into DB
     * 
     * @param array $numbers    The new permutation
     * 
     * @return Int the Id of the new permutation
     */
    private function _insertPermutation($numbers = array())
    {
        if (!is_array($numbers))
            throw new Exception("We needed an array of integer here!");
        $sql = 'Insert into numbers (`flat`, `' . implode('`, `', range(0, count($numbers) - 1)) . '`) values (?' . str_repeat(', ?', count($numbers)) . ')';
        $params = array("'" . implode(", ", $combinations) ."'");
        foreach($numbers as $index => $number)
        {
            if(!is_numeric($number))
                throw new Exception("\$numbers[$index](= $number) is not a number!");
            $sql .= ', ?';
            $params[] = "'" . $number . "'";
        }
        $sql .=')';
        $sth = $this->_connect()->prepare($sql);
        $sth->execute($params);
        $result = $sth->fetch(PDO::FETCH_ASSOC);
        if(!isset($result['id']))
            throw new Exception("Error Insert new permutation!");
        return $result['id'];
    }
    /**
     * Finding the permutation and gametype relationship
     *
     * @param Int $permId     The permutation id
     * @param Int $gameTypeId The game type id
     *
     * @return Int Apearing times
     */
    private function _updatePerGameType($permId, $gameTypeId)
    {
        $rel = $this->getPermGameType($permId, $gameTypeId);
        $apearedTimes = 1;
        //we have this relationship before
        if(isset($rel['id']) && is_numeric(trim($rel['id'])))
        {
            $apearedTimes = trim($rel['apearedTimes']) + 1;
        }
        $sth = $this->_connect()->prepare('REPLACE INTO numbers_gametype(`numbersId`, `gametypeId`, `apearedTimes`) values(?, ?, ?);');
        $sth->execute(array($permId, $gameTypeId, $apearedTimes));
        return $apearedTimes;
    }
    /**
     * insert new numbers into DB
     * 
     * @param array $numbers    The new permutation
     * @param int   $gameTypeId The game type id
     * 
     * @return NumberDB
     */
    public function newPermutation($numbers = array(), $gameTypeId)
    {
        //get the game type id
        $gameType = $this->getGameType($gameTypeId);
        $gameTypeId = trim($gameType['id']);
        if(is_numeric($gameTypeId))
            throw new Exception("We needed an array of integer here!");
        
        //get the permutation id
        $permutation = $this->findPermutation($numbers);
        if(!isset($permutation['id']) || !is_numeric(trim($permutation['id'])))
            $permutationId = $this->_insertPermutation($numbers);
        else
            $permutationId = trim($permutation['id']);
        
        //insert the relationship
        $this->_updatePerGameType($permutationId, $gameTypeId);
        return $this; 
    }
}